package cn.com.nes.site.module.dao.crm;

import cn.com.nes.common.util.*;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import java.net.URLDecoder;
import java.util.*;

@Repository
public class OpportunityDaoImpl extends BaseDaoImpl {

    private TransactionStatus newg_status = null;

    public PageData getOpportunityList(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取商机信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer() ;
            sb.append(" select a.projectarea,a.projectaddress,a.memo,date_format(a.mobilizationtime,'%Y-%m-%d') mobilizationtime,date_format(a.completetime,'%Y-%m-%d') completetime,date_format(a.ordergoodstime,'%Y-%m-%d') ordergoodstime," +
            		"a.equinumber,a.buildnature,a.bulidtype,a.equispec,a.equispecsize, a.customer_house_id as houseId," +
            		"a.cgftype,a.cgfphone,a.sjfcompanyname,a.sjfphone,a.cjfcompanyname,a.cjfphone," +
            		"a.tzfcompanyname,a.tzfphone,a.jlfcompanyname,a.jlfphone," +
            		" a.uuid,a.opportunityname,a.clueId,a.customerId,b.customername customerName, " +
            		" a.personincharge,a.opportunitystage,c.`name` opportunitystageName,a.opportunitytype,d.`name` opportunitytypeName,a.opportunitymoney,a.estimatedclosingdate, \n" +
                    " (select count(1)from t_project pro where pro.opportunity_uuid=a.uuid ) proCount " +
                    " ,a.category_of_declaration as categoryOfDeclaration, g.name as categoryOfDeclarationName" +
            		" from opportunity a\n" +
                    " left join customer b on a.customerId=b.uuid\n" +
                    " left join t_param c on a.opportunitystage=c.value and c.param_type='16'\n" +
                    " left join t_param d on a.opportunitytype=d.value and d.param_type='17'\n" +
                    " left JOIN (SELECT m.opportunityId,max(m.visittime) visittime,max(m.modifyTime) vMaxTime,m.uuid,m.userid ,m.username,m.visitaddress,m.visitcontent ").append("from log_claim m GROUP BY opportunityId ) e on a.uuid=e.opportunityId  ")
            .append(" left JOIN (SELECT m.opportunityId,max(m.createTime) vMaxTime ").append("from log_operate m GROUP BY opportunityId ) f on a.uuid=f.opportunityId  ")
            .append(" left JOIN t_param g on g.value=a.category_of_declaration and g.param_type='326'\n")
            .append("where a.isdelete='0' and a.companyId like concat('"+req.getUinfo().getCompanyId()+"','%') ");

            if(!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
                //区分是否为管理员 '04'是管理员
                String userType=req.getUinfo().getUserType();
                if(!"04".equals(userType)){
                    sb.append(" and a.opportunityowner='").append(req.getUinfo().getId()).append("' ");
                }
            }

            if (!StringUtils.isEmpty((String) param.get("opportunityname"))){
                sb.append(" and a.opportunityname like '").append(param.get("opportunityname")).append("%' ");
            }
            if(!StringUtils.isEmpty((String) param.get("customername"))){
                sb.append(" and b.customername like '").append(param.get("customername")).append("%' ");
            }
            if (!StringUtils.isEmpty((String) param.get("opportunitystage"))){
                sb.append(" and a.opportunitystage='").append(param.get("opportunitystage")).append("' ");
            }
            if (!StringUtils.isEmpty((String) param.get("personincharge"))){
                sb.append(" and e.nickName like '").append(param.get("personincharge")).append("%' ");
            }
            if (!StringUtils.isEmpty((String) param.get("clueUuid"))) {
                sb.append(" and a.clueId = '").append(param.get("clueUuid")).append("' ");
            }

			//最近三个月预期签单
			if(!StringUtils.isEmpty((String)req.getReqData().get("Expectedsigning")) && "02".equals((String)req.getReqData().get("Expectedsigning"))) {
				sb.append("  and a.estimatedclosingdate<=date_add(now(),interval 3 month) ");
			}
			//关联客户
			if(!StringUtils.isEmpty((String)req.getReqData().get("RelatedCustomers")) && "02".equals((String)req.getReqData().get("RelatedCustomers"))) {
				sb.append("  and a.customerId is not null and trim(a.customerId) <>''  ");
			}
			
            //行政区划
			if(!StringUtils.isEmpty((String) param.get("xzqhdm")) && !"86".equals((String) req.getReqData().get("xzqhdm"))) {
				sb.append("  and a.xzqhdm like  '").append(XzqhUtil.getQueryXzqh(this.getJdbcTemplate_newg(),(String)req.getReqData().get("xzqhdm"))).append("%' ");
			}
			
			//工作台数据概览跳转查询
			if(!StringUtils.isEmpty((String) req.getReqData().get("dateType"))){
            	String dateType = String.valueOf(req.getReqData().get("dateType")) ;
            	String query = dateTypeCondition_mod(dateType);
            	sb.append(query);
			}
			//商机所有人
			if (!StringUtils.isEmpty((String) param.get("opportunityowner"))){
                sb.append(" and a.opportunityowner = '").append(param.get("opportunityowner")).append("' ");
            }
			
			//工作台待办跳转查询
			if(!StringUtils.isEmpty((String) param.get("wb_db"))){
				if("01".equals(param.get("wb_db"))) {
					sb.append(" and a.uuid not in (select a.opportunityId from log_claim a where a.isdelete=0 and a.type='03' ) ");
				}else if("02".equals(req.getReqData().get("wb_db"))) {
					sb.append("and (e.vMaxTime<date_sub(curdate(),INTERVAL 30 day) or f.vMaxTime<date_sub(curdate(),INTERVAL 30 day)) and a.opportunitystage!='06' and a.opportunitystage!='07' ");
				}
			}

            sb.append(" order by a.createTime desc ");
            sb = DBUtils.sql_check(sb) ;
            sbfrom.append(sb.toString()) ;
            int idx=sbfrom.indexOf(" from ") ;
            sbfrom.delete(0, idx);
            Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getOpportunityDetail(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        Map<String,Object> extData = new LinkedHashMap<String, Object>();
        res.setRetCode(9);
        res.setRetMessage("获取商机信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            //获取商机内容
            sb.delete(0,sb.length());
            sb.append("select date_format(a.mobilizationtime,'%Y-%m-%d') mobilizationtime,date_format(a.completetime,'%Y-%m-%d') completetime,date_format(a.ordergoodstime,'%Y-%m-%d') ordergoodstime," +
            		"a.equinumber,a.buildnature,a.bulidtype,a.equispec,a.equispecsize," +
            		"a.cgftype,a.cgfphone,a.sjfcompanyname,a.sjfphone,a.cjfcompanyname,a.cjfphone," +
            		"a.tzfcompanyname,a.tzfphone,a.jlfcompanyname,a.jlfphone," +
            		"a.jsondata,a.templateId,a.uuid,a.opportunityname, " +
            		"a.clueId,f.cluename clueName,f.projectaddress clueProjectAddress,f.projectarea clueProjectArea,f.picture1 cluePicture1,f.picture2 cluePicture2,f.picture3 cluePicture3, " +
            		"a.customerId,b.customername customerName,b.personincharge customerPersonincharge,b.phone customerPhone,b.address customerAddress,b.cluecompany companyname,\n" +
                    "a.opportunitystage,c.`name` opportunitystageName,a.opportunitytype,d.`name` opportunitytypeName,\n" +
                    "a.xzqhdm,g.xzqhmc,a.projectaddress,a.projectarea,a.longitude,a.latitude,a.personincharge,\n" +
                    "a.sex,a.wechat,a.memo,a.picture1,a.picture2,a.picture3,a.opportunitymoney,a.estimatedclosingdate\n" +
                    " ,a.category_of_declaration as categoryOfDeclaration, h.name as categoryOfDeclarationName\n" +
                    "from opportunity a\n" +
                    "left join customer b on a.customerId=b.uuid\n" +
                    "left join t_param c on a.opportunitystage=c.value and c.param_type='16'\n" +
                    "left join t_param d on a.opportunitytype=d.value and d.param_type='17'\n" +
                    "left join clue f on a.clueId=f.uuid\n" +
                    "left join t_xzqh g on g.xzqhdm=a.xzqhdm\n" +
                    "left join t_param h on h.value=a.category_of_declaration and h.param_type='326'\n" +
                    "where a.isdelete=0 and a.uuid=?");

            List<Map<String, Object>> opportunityList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId")});
            extData.put("opportunityInfo",opportunityList.size()>0?opportunityList.get(0):null);
			if (CollUtil.isNotEmpty(opportunityList)) {
                Map<String,Object> detail = (Map<String,Object>) opportunityList.get(0);
                //获取商机图片
                for(int i=0;i<3;i++){
                    if(!StringUtils.isEmpty((String) detail.get("picture"+(i+1)))){
                        //压缩
                        String urlStr1 = OssUtil.getImgSignURL((String) detail.get("picture"+(i+1)), 1);
                        detail.put("imgReal"+(i+1),urlStr1);
                        //不压缩
                        String urlStr1_1 = OssUtil.getImgSignURL((String) detail.get("picture"+(i+1)), 0);
                        detail.put("imgRealBig"+(i+1),urlStr1_1);
                    }else{
                        detail.put("imgReal"+(i+1),"");
                        detail.put("imgRealBig"+(i+1),"");
                    }
                }
                //获取线索图片
                for(int i=0;i<3;i++){
                    if(!StringUtils.isEmpty((String) detail.get("cluePicture"+(i+1)))){
                        //压缩
                        String urlStr1 = OssUtil.getImgSignURL((String) detail.get("cluePicture"+(i+1)), 1);
                        detail.put("clueImgReal"+(i+1),urlStr1);
                        //不压缩
                        String urlStr1_1 = OssUtil.getImgSignURL((String) detail.get("cluePicture"+(i+1)), 0);
                        detail.put("clueImgRealBig"+(i+1),urlStr1_1);
                    }
                }
            }
            
            //获取跟进记录
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.clueId,a.customerId,a.opportunityId,a.projectId,a.type,b.`name` typeName,a.userid,a.username,a.visittype,c.`name` visittypeName,a.visitaddress,a.visittime,a.visitcontent,a.createTime " +
					" from log_claim a " +
					" left join t_param b on a.type=b.`value` and b.param_type='18' " +
					" left join t_param c on a.visittype=c.`value` and c.param_type='19' " +
					" where a.isdelete=0 and a.type='03' and a.opportunityId=? " +
					" order by a.createTime desc");
            List<Map<String, Object>> claimList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId")});
            extData.put("claimList",claimList);

            //获取客户联系人
            sb.delete(0,sb.length());
            sb.append("select a.uuid,c.uuid customerId,c.customername,a.post,a.department,a.sex,a.ismain,a.name,a.phone,a.wechat " +
					 " from opportunity_staff a " +
					 " left join customer c on a.customerid=c.uuid " +
					 " where a.isdelete=0  and a.opportunityId=?" +
					 " order by a.createTime desc");
            List<Map<String, Object>> staffList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId")});
            extData.put("staffList",staffList);

            //获取文件列表
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.opportunityId,b.opportunityname,a.filetype,c.`name` filetypeName,a.filename,a.filepath,a.memo,a.createUid,d.nickName,a.createTime " +
					" from opportunity_file a " +
            		" left join opportunity b on a.opportunityId=b.uuid " +
            		" left join t_param c on a.filetype=c.`value` and c.param_type='29' " +
            		" left join t_user d on a.createUid=d.id" +
            		" where a.isdelete=0 and a.opportunityId=? " +
            		" order by a.createTime desc");
            List<Map<String, Object>> fileList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId")});
            extData.put("fileList",fileList);
            
          //获取商机文件真实路径
            Iterator<Map<String, Object>> iterator = fileList.iterator();
            while(iterator.hasNext()){
            	Map<String, Object> next = iterator.next();
            	String filepath = (String) next.get("filepath");
            	String imgSignURL = OssUtil.getFileSignURL(filepath, 0);
            	next.put("realFilePath", imgSignURL);
            }
            
            //获取报价单
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.title,a.customerId,b.customername,a.quoteDate,a.personincharge,a.quoteMoney " +
					" from opportunity_quote a " +
					" left join customer b on a.customerId=b.uuid " +
					" where a.isdelete=0 and a.opportunityId=? " +
					" order by a.createTime desc");
            List<Map<String, Object>> quoteList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId")});
            extData.put("quoteList",quoteList);
            
          //获取设计图清单
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.bimId,a.dataId,a.type,a.`name`,a.spec,a.icon,a.count,a.unit," +
            		" a.brand,a.price,a.lenX,a.lenY,a.lenZ,a.style,a.levelId,a.pipeType,a.levelNo,a.diameter,a.height," +
            		"a.length,a.materialType,a.shape,a.roomIds,a.area,a.deviceId,a.series,a.circuit " +
					" from bim_design a " +
					" left join opportunity_bim b on a.bimId=b.bimId " +
					" where a.isdelete=0 and b.opportunityId=?" +
					" order by a.type desc");
            List<Map<String, Object>> designList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId")});
            extData.put("designList",designList);

            //获取设计图
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.bimId,a.tid,a.uid,a.title,a.pic,a.memo,a.designUrl,a.shareUrl,a.sharePermission,a.createTime " +
					" from opportunity b  " +
            		" inner join opportunity_bim a on b.bimId=a.bimId " +
					" where b.isdelete=0 and b.uuid=? ");
            List<Map<String, Object>> bimList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId")});
            extData.put("bimList",bimList);

            //获取合同
            sb.delete(0,sb.length());
            sb.append(" ");
            List<Map<String, Object>> contractList = new ArrayList<Map<String,Object>>();//this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId")});
            extData.put("contractList",contractList);

            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
    
    public PageData saveOpportunity(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		
		param.put("tableName", "opportunity");
        param.put("createUid",req.getUinfo().getId());
        param.put("isdelete","0");
        param.put("opportunityowner",req.getUinfo().getId());
        param.put("companyId", req.getUinfo().getCompanyId());
		String clueId = (String) param.get("clueId");
		//添加操作日志
		param.put("OperationLogType", "03");
		param.put("OperationLogId", "updateOpportunityId");
		
		if(StringUtils.isEmpty((String) param.get("opportunityname"))){
        	res.setRetCode(9);
            res.setRetMessage("商机名称不能为空!");
            return res;
        }
        if(StringUtils.isEmpty((String) param.get("customerId"))){
        	res.setRetCode(9);
            res.setRetMessage("所属客户不能为空!");
            return res;
        }
        if(!StringUtils.isEmpty((String) param.get("opportunitystage")) && "03".equals((String) param.get("opportunitystage")) ){
        	res.setRetCode(9);
            res.setRetMessage("商机已经成交，不能编辑!");
            return res;
        }
        if(!StringUtils.isEmpty((String) param.get("opportunitystage")) &&  "04".equals((String) param.get("opportunitystage"))){
        	res.setRetCode(9);
            res.setRetMessage("商机已经流失，不能编辑!");
            return res;
        }
        if(StringUtils.isEmpty((String) param.get("estimatedclosingdate"))){
        	param.put("estimatedclosingdate", null);
        }
        newg_status = begin_newg();// 开启事物
		try {
			//拓展字段用
			String jsondata = (String) param.get("jsondata") ;
			jsondata =  URLDecoder.decode(jsondata, "UTF-8");
			param.put("jsondata", jsondata);
			//拓展字段用
			
			//为空报错
			param.put("longitude",null);
			param.put("latitude",null);
			param.put("opportunitymoney",null);
			if(StringUtils.isEmpty((String) param.get("projectarea"))){
	        	param.put("projectarea", null);
	        }
			//为空报错
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("opportunitystage","01");
				param.put("OperationLogName", "增加商机");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条商机信息");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改商机");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条商机信息");

                // 处理修改商机，入参的【预计进场日期、预计竣工日期、预计订货日期】为空的报错
                if (ObjectUtil.isNotNull(param.get("mobilizationtime")) && "".equals(String.valueOf(param.get("mobilizationtime")))) {
                    param.put("mobilizationtime", null);
                }
                if (ObjectUtil.isNotNull(param.get("completetime")) && "".equals(String.valueOf(param.get("completetime")))) {
                    param.put("completetime", null);
                }
                if (ObjectUtil.isNotNull(param.get("ordergoodstime")) && "".equals(String.valueOf(param.get("ordergoodstime")))) {
                    param.put("ordergoodstime", null);
                }

				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
	   		    res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				res.setRetCode(9);
				res.setRetMessage(res.getRetMessage());
			}
			//修改线索阶段为转商机阶段
			StringBuffer sb = new StringBuffer();
			sb.append("update clue set cluestage='04' where uuid='").append(clueId).append("' ");
			this.getJdbcTemplate_newg().update(sb.toString(),new Object[]{});
			commit_newg(newg_status);
			
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}
    
    @SuppressWarnings("unchecked")
    public PageData delOpportunity(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("商机删除失败");
        

        if(isOpportunityInProject(String.valueOf(param.get("uuid")))){
        	res.setRetCode(9);
            res.setRetMessage("此商机已加入工程");
            return res;
        }
        if(isOpportunityInContract(String.valueOf(param.get("uuid")))){
        	res.setRetCode(9);
            res.setRetMessage("此商机已生成合同，请先删除相关合同！");
            return res;
        }
        
        param.put("tableName", "opportunity") ;
        param.put("keys", "uuid") ;
        param.put("isdelete", "1");
        newg_status = begin_newg();// 开启事物
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了"+param.get("opportunityname")+"商机");
            param.put("opportunityId", param.get("uuid"));
            SaveOperatelog(req,"03","删除商机信息",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	 
        }
        return res;
    }
    
    //判断商机是否关联工程
    public boolean isOpportunityInProject(String opportunityId) {
		List<Map<String, Object>> list = null;
		boolean result = false;
        try {
            StringBuffer sb = new StringBuffer();
            sb.delete(0,sb.length());
            sb.append("select count(1) cnt from t_project pro where pro.opportunity_uuid=?");

            list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{opportunityId});
            if(Integer.parseInt(String.valueOf(list.get(0).get("cnt")))>0){
            	result = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return result;
    }
    //判断商机是否关联合同
    public boolean isOpportunityInContract(String opportunityId) {
		List<Map<String, Object>> list = null;
		boolean result = false;
        try {
            StringBuffer sb = new StringBuffer();
            sb.delete(0,sb.length());
            sb.append("select count(1) cnt from contract_info pro where pro.opportunity_id=? and deleted=0 ");

            list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{opportunityId});
            if(Integer.parseInt(String.valueOf(list.get(0).get("cnt")))>0){
            	result = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return result;
    }  
    
    @SuppressWarnings("unchecked")
    public PageData opportunityLoss(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("商机流失设置失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "opportunity") ;
        param.put("keys", "uuid") ;
        param.put("opportunitystage", "07");
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
            param.put("OperationMemo", req.getUinfo().getNickName()+"设置商机"+param.get("opportunityname")+"为商机流失");
            SaveOperatelog(req,"03","设置商机流失",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	
        }
        return res;
    }
 
    public PageData getOpportunityDynamic(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        Map<String,Object> extData = new LinkedHashMap<String, Object>();
        res.setRetCode(9);
        res.setRetMessage("获取商机动态失败");
        try {
            StringBuffer sb = new StringBuffer();
            //获取商机动态
            sb.delete(0,sb.length());
            sb.append("select * " +
					" from ( " +
					" select a.uuid,a.clueId,a.customerId,a.opportunityId,a.projectId,a.type,b.`name` typeName,a.userId,a.username,a.logdate,a.visittype,c.`name` visittypeName,a.visitaddress,a.visittime,a.visitcontent content,'' memo,1 flag " +
					" from log_claim a " +
					" left join t_param b on a.type=b.`value` and b.param_type='18' " +
					" left join t_param c on a.visittype=b.`value` and b.param_type='19' " +
					" where a.isdelete=0 and a.opportunityId=? " +
					" union  " +
					" select a.uuid,a.clueId,a.customerId,a.opportunityId,a.projectId,a.type,b.`name` typeName,a.userId,a.username,a.logdate,'' visittype,'' visittypeName,'' visitaddress,'' visittime,a.remark content,a.memo ,2 flag " +
					" from log_operate a " +
					" left join t_param b on a.type=b.`value` and b.param_type='18' " +
					" where a.opportunityId=? " +
					" ) l " +
					" GROUP BY l.logdate desc");

            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("opportunityId"),param.get("opportunityId")});

            res.setExtData(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
    
    public PageData saveOpportunityClaim(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "log_claim");
		param.put("type", "03");
		
		//添加操作日志
		param.put("OperationLogType", "03");
		param.put("OperationLogId", "updateOpportunityId");
		param.put("companyId", req.getUinfo().getCompanyId());
		
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加跟进记录");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条跟进记录");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改跟进记录");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条跟进记录");
				res =  updateTableData(newg_status, req) ;
			}
			
			//修改商机跟进状态
			updateOpportunityStage(newg_status,req);
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				res.setRetCode(9);
				res.setRetMessage("Error");
				rollback_newg(newg_status);
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
    
    @SuppressWarnings("unchecked")
    public PageData delOpportunityClaim(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("跟进记录删除失败");
        newg_status = begin_newg();// 开启事物
        param.put("tableName", "log_claim");
     
        try {
        	param.put("OperationMemo", req.getUinfo().getNickName()+"删除了一条跟进记录");
        	param.put("uuid", param.get("claimId"));
        	delClaimlog(req,"03",newg_status);
            commit_newg(newg_status);
            res.setKey((String)param.get("uuid")) ;
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
        	
        }
        return res;
    }
    
    public PageData saveOpportunityStaff(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "opportunity_staff");
        param.put("createUid",req.getUinfo().getId());
        param.put("isdelete","0");
		
		//添加操作日志
		param.put("OperationLogType", "03");
		param.put("OperationLogId", "updateOpportunityId");
		
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加商机联系人信息");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条商机联系人信息");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改商机联系人信息");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条商机联系人信息");
				res =  updateTableData(newg_status, req) ;
			}
			
			//修改商机跟进状态
			updateOpportunityStage(newg_status,req);
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				res.setRetCode(9);
				res.setRetMessage(res.getRetMessage());
				rollback_newg(newg_status);
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
    
    @SuppressWarnings("unchecked")
    public PageData delOpportunityStaff(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("商机联系人删除失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "opportunity_staff") ;
        param.put("keys", "uuid") ;
        param.put("isdelete", "1");
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了一条商机联系人信息");
            SaveOperatelog(req,"03","删除商机联系人信息",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	
        }
        return res;
    }
    
    public PageData addOpportunityFile(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "opportunity_file");
		param.put("isdelete", "0");
		
		//添加操作日志
		param.put("OperationLogType", "03");
		param.put("OperationLogId", "updateOpportunityId");
		
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加商机文件");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条商机文件");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改商机文件");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条商机文件");
				res =  updateTableData(newg_status, req) ;
			}
			
			//修改商机跟进状态
			updateOpportunityStage(newg_status,req);
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				res.setRetCode(9);
				res.setRetMessage("Error");
				rollback_newg(newg_status);
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
    
    @SuppressWarnings("unchecked")
    public PageData delOpportunityFile(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("商机文件删除失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "opportunity_file") ;
        param.put("keys", "uuid") ;
        param.put("isdelete", "1");
        param.put("uuid", param.get("fileId"));
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了一条商机文件");
            SaveOperatelog(req,"03","删除商机文件信息",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	
        }
        return res;
    }
    
    public PageData addOpportunityQuote(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "opportunity_quote");
        param.put("createUid",req.getUinfo().getId());
        param.put("createTime",new Date());
        param.put("isdelete","0");
		
		//添加操作日志
		param.put("OperationLogType", "03");
		param.put("OperationLogId", "updateOpportunityId");
		
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加报价单信息");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条报价单信息");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改报价单信息");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条报价单信息");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				res.setRetCode(9);
				res.setRetMessage("Error");
				rollback_newg(newg_status);
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
    
    @SuppressWarnings("unchecked")
    public PageData delOpportunityQuote(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("商机报价单删除失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "opportunity_quote") ;
        param.put("keys", "uuid") ;
        param.put("isdelete", "1");
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了一条报价单信息");
            SaveOperatelog(req,"03","删除商机报价单信息",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	
        }
        return res;
    }
    
    public PageData getQuoteDetail(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        Map<String,Object> extData = new LinkedHashMap<String, Object>();
        res.setRetCode(9);
        res.setRetMessage("获取报价单详情失败");
        try {
            StringBuffer sb = new StringBuffer();
            //获取报价单详情
            sb.delete(0,sb.length());
            sb.append("select a.uuid quoteId,a.title,a.customerId,b.customername,a.opportunityId,c.opportunityname, " +
            		" a.contact,a.quoteDate,a.effectiveDate,a.personincharge,a.quoteMoney,a.discount,a.cost,a.profit,a.memo " +
					" from opportunity_quote a " +
					" left join customer b on a.customerId=b.uuid " +
					" left join opportunity c on a.opportunityId=c.uuid " +
					" where a.uuid=?");

            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("quoteId")});
            extData.put("quoteInfo", list.size()>0?list.get(0):null);
            //获取报价单产品列表
            sb.delete(0, sb.length());
            sb.append("select b.uuid productId,b.productName,b.productType,d.`name` productTypeName,b.productModel, " +
            		" b.equipmentCompany,c.priceType,e.`name` priceTypeName,c.price " +
            		" from quote_product a " +
            		" left join product b on a.productId=b.uuid " +
            		" left join product_price c on b.uuid=c.productId " +
            		" left join t_param d on b.productType=d.`value` and d.param_type='21' " +
            		" left join t_param e on c.priceType=e.`value` and e.param_type='22' " +
            		" where a.quoteId=?");
            List<Map<String, Object>> productList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("quoteId")});
            extData.put("productList", productList);
            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
    
    @SuppressWarnings("unchecked")
    public PageData transferOpportunity(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("商机转移失败");
        
        newg_status = begin_newg();// 开启事物
        if(isOpportunityInProject(String.valueOf(param.get("uuid")))){
        	res.setRetCode(9);
            res.setRetMessage("此商机已加入工程");
            return res;
        }
        if(isOpportunityInContract(String.valueOf(param.get("uuid")))){
        	res.setRetCode(9);
            res.setRetMessage("此商机已生成合同，请先删除相关合同！");
            return res;
        }
        param.put("tableName", "opportunity") ;
        param.put("keys", "uuid") ;
        param.put("modifyUid", req.getUinfo().getId());
        
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
            
            //获取所属人信息
            StringBuffer sb = new StringBuffer();
            sb.append("select nickName from t_user where id='").append(param.get("opportunityowner")).append("' ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            
            param.put("OperationMemo", req.getUinfo().getNickName()+"转移了一条商机给"+list.get(0).get("nickName"));
            SaveOperatelog(req,"03","商机转移",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	
        }
        return res;
    }
    
    //获取bim设计列表
    public PageData getBimList(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取设计图失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            //获取设计图列表
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.bimId,a.tid,a.uid,a.title,a.pic,a.memo,a.designUrl,a.shareUrl,a.sharePermission,a.createTime, if(b.uuid is not null and b.uuid!='',1,0) isRelated" +
					" from opportunity_bim a " +
            		" left join opportunity b on a.bimId=b.bimId " +
					" where a.isdelete=0");
            if(!StringUtils.isEmpty((String) param.get("bimId"))){
            	sb.append(" and a.bimId='").append(param.get("bimId")).append("' ");
            }
            if(!StringUtils.isEmpty((String) param.get("title"))){
            	sb.append(" and a.title like '%").append(param.get("title")).append("%' ");
            }
            if(!StringUtils.isEmpty((String) param.get("opportunityId"))){
            	sb.append(" and a.opportunityId='").append(param.get("opportunityId")).append("' ");
            }
            sb.append(" group by a.uuid ");
            sb = DBUtils.sql_check(sb) ;
            sbfrom.append(sb.toString()) ;
            sbfrom.insert(0,"select count(1) cnt from (");
            sbfrom.append(") l");
            int idx=sbfrom.indexOf(" from ") ;
            sbfrom.delete(0, idx);
            
            Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
            List<Map<String, Object>> bimList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setPage(bimList);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
    
    @SuppressWarnings("unchecked")
    public PageData delBim(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("设计图删除失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "opportunity_bim") ;
        param.put("keys", "bimId") ;
        param.put("modifyUid", req.getUinfo().getId());
        param.put("isdelete", "1");
        try {
        	//判断商机是否关联设计图
        	StringBuffer sb = new StringBuffer();
        	sb.append("select count(1) cnt from opportunity where  bimId='").append(param.get("bimId")).append("' ");
            int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), Integer.class);
            if(cnt>0) {
            	res.setRetCode(9);
                res.setRetMessage("不能删除关联商机的设计图!");
                return res;
            }
        	PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
            
            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了设计图");
            SaveOperatelog(req,"03","删除设计图",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	 
        }
        return res;
    }
    
    @SuppressWarnings("unchecked")
    public PageData delOpportunityBim(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("商机设计图删除失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "opportunity") ;
        param.put("keys", "uuid") ;
        param.put("modifyUid", req.getUinfo().getId());
        param.put("bimId", "");
        try {
        	param.put("uuid", param.get("opportunityId"));
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
            
            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了商机设计图");
            SaveOperatelog(req,"03","删除商机设计图",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	
        }
        return res;
    }
    
    @SuppressWarnings("unchecked")
    public PageData addOpportunityBim(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("商机设计图添加失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "opportunity") ;
        param.put("keys", "uuid") ;
        param.put("modifyUid", req.getUinfo().getId());
        try {
            
        	PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
            
			//修改商机跟进状态
			updateOpportunityStage(newg_status,req);
            
            param.put("OperationMemo", req.getUinfo().getNickName()+"添加了商机设计图");
            SaveOperatelog(req,"03","添加商机设计图",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	 
        }
        return res;
    }
    
    
    //添加bim
    @SuppressWarnings("unchecked")
    public PageData addBim(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("bim设计添加失败");
        newg_status = begin_newg();// 开启事物
        param.put("tableName", "opportunity_bim");
//        param.put("createUid",req.getUinfo().getId());
        param.put("createTime",new Date());
        param.put("isdelete","0");
        
        try {
            PageData sqlInfo = getInsertSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
            
            commit_newg(newg_status);
            res.setKey((String) param.get("uuid")) ;
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
        	 
        }
        return res;
    }
    
  //修改商机清单
    @SuppressWarnings("unchecked")
	public PageData updateBim(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("bim设计修改失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "opportunity_bim");
		param.put("keys", "bimId") ;
		try {
			PageData sqlInfo = getUpdateSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				System.out.println("修改失败 -- "+" bimId"+param.get("bimId"));
				rollback_newg(newg_status);
				return sqlInfo;
			}
			StringBuffer sb_field = null ;
			List<String> listData =  null ;
			
			sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
			
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
    
  //添加设计清单
    @SuppressWarnings("unchecked")
    public PageData addDesignDetail(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("bim清单添加失败");
        newg_status = begin_newg();// 开启事物
        param.put("tableName", "bim_design");
        
        try {
            PageData sqlInfo = getInsertSQL(req);
            if (sqlInfo.getRetCode() != 0) {
            	System.out.println("添加失败 -- type:"+param.get("type")+" bimId"+param.get("bimId")+" dataId:"+param.get("dataId"));
                rollback_newg(newg_status);
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
            
            commit_newg(newg_status);
            res.setKey((String) param.get("uuid")) ;
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
        	 
        }
        return res;
    }
    
    //修改商机清单
    @SuppressWarnings("unchecked")
	public PageData updateDesignDetail(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("bim清单修改失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "bim_design");
		param.put("keys", "dataId;bimId") ;
		try {
			PageData sqlInfo = getUpdateSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				System.out.println("修改失败 -- type:"+param.get("type")+" bimId"+param.get("bimId")+" dataId:"+param.get("dataId"));
				rollback_newg(newg_status);
				return sqlInfo;
			}
			StringBuffer sb_field = null ;
			List<String> listData =  null ;
			
			sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
			
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
    
    //获取商机清单是否存在
    public boolean isExistDesign(String dataId,String bimId){
    	boolean result = false;
        try {
            StringBuffer sb = new StringBuffer();
            //获取设计图列表
            sb.delete(0,sb.length());
            sb.append("select count(1) cnt from bim_design where dataId='").append(dataId).append("' and bimId='").append(bimId).append("' ");
            
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
           if(Integer.parseInt(String.valueOf(list.get(0).get("cnt")))>0){
        	   result = true;
           }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return result;
    }
    
  //获取清单列表
    public PageData getDesignList(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取设计图清单失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            //获取清单列表
            sb.delete(0,sb.length());
            sb.append("select c.uuid,c.dataId,a.`name` levelName,if(b.name!='' and b.name is not null,b.name,b.functionType) roomName,c.`name` ,c.icon, " +
            		" c.spec,c.materialType,c.brand,c.series,c.count,c.length,c.area,c.unit,c.price " +
            		" from bim_design a " +
            		" left join bim_design b on a.dataId=b.levelId and a.bimId=b.bimId " +
            		" left join bim_design c on c.roomIds like concat('%',b.dataId,'%') and a.bimId=c.bimId " +
            		" left join opportunity_bim d on a.bimId=d.bimId " +
            		" where c.isdelete=0 and a.type='楼层' and (c.bimId='").append(param.get("bimId")).append("' ")
            		.append(" or d.opportunityId='").append(param.get("opportunityId")).append("')");
            if(!StringUtils.isEmpty((String) param.get("name"))){
            	sb.append(" and (a.name like '%").append(param.get("name")).append("%' ");
            	sb.append(" or if(b.name!='' and b.name is not null,b.name,b.functionType) like '%").append(param.get("name")).append("%' ) ");
            }
            if(!StringUtils.isEmpty((String) param.get("levelNo"))){
            	sb.append(" and a.levelNo=").append(param.get("levelNo"));
            }
            if(!StringUtils.isEmpty((String) param.get("roomType"))){
            	sb.append(" and b.functionType='").append(param.get("roomType")).append("' ");
            }
            sb.append(" group by c.dataId");
            
            sb = DBUtils.sql_check(sb) ;
            sbfrom.append(sb.toString()) ;
            int idx=sbfrom.indexOf(" from ") ;
            sbfrom.delete(0, idx);
            sbfrom.insert(0, " from (select 1 ");
            sbfrom.append(" ) l ");
            Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
            List<Map<String, Object>> designList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setPage(designList);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
    
    public PageData getLevelList(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取楼层失败");
        try {
            StringBuffer sb = new StringBuffer();
            //获取层数列表
            sb.delete(0,sb.length());
            sb.append(" select a.name,a.levelNo from bim_design a left join opportunity_bim b on a.bimId=b.bimId where a.isdelete=0 and a.type='楼层' and (a.bimId='"+param.get("bimId")+"' or b.opportunityId='").append(param.get("opportunityId")).append("') ");
            
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setExtData(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
    
    public PageData getRoomList(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取房间失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            //获取房间列表
            sb.delete(0,sb.length());
            sb.append(" select a.functionType `name` from bim_design a left join opportunity_bim b on a.bimId=b.bimId where a.isdelete=0 and a.type='房间' and (a.bimId='"+param.get("bimId")+"' or b.opportunityId='").append(param.get("opportunityId")).append("') ");
            
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setExtData(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
    
    public PageData chageOpportunityStage(String opportunityId,String opportunitystage) {
    	
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("修改商机状态失败");
		try {
			String sb=" update opportunity set opportunitystage='"+opportunitystage+"' where uuid='"+opportunityId+"' ";
			this.getJdbcTemplate_newg().update(sb);
			res.setRetCode(0);
            res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
    }

}
